﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;
using System.Data.SqlClient;

namespace InSys.Documents
{
    public partial class FormExcelImport : Form
    {
        private MapTestLinqDataContext db;
        public FormExcelImport()
        {
            InitializeComponent();
        }

        private void FormExcelImport_Load(object sender, EventArgs e)
        {
            db = new MapTestLinqDataContext(Properties.Settings.Default.testlinqConnectionString);
            db.ObjectTrackingEnabled = false;
            personBindingSource.DataSource = db.persons;
        }

        private void selectFileButton_Click(object sender, EventArgs e)
        {
                OpenFileDialog openFileDialog1 = new OpenFileDialog();
                openFileDialog1.InitialDirectory = "c:\\" ;
                openFileDialog1.Filter = "excel files (*.xls)|*.xls|All files (*.*)|*.*" ;
                openFileDialog1.FilterIndex = 1 ;
                openFileDialog1.RestoreDirectory = true ;

                if (openFileDialog1.ShowDialog() == DialogResult.OK)
                {
                    fileNameTextBox.Text = openFileDialog1.FileName;
                }
        }

        private void ImportButton_Click(object sender, EventArgs e)
        {
            string excelConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+fileNameTextBox.Text+";Persist Security Info=False;Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"";
            string sqlDeletePerson_Documents = "DELETE FROM dbo.person_document";
            string sqlDeleteDocument = "DELETE FROM dbo.document";
            string sqlInsertDocument = "INSERT INTO dbo.document (local_id, date, number, description, enterprise, finished, id_kindDocument, address) VALUES (@number, @regdate1, @regnum1, @description, @enterprise, 0, @kindDocument, '')";
            string sqlInsertPerson = "INSERT INTO dbo.person_document (id_person, id_document, datetime, description, id_description) VALUES (@id_person, @id_document, @datetime, @description, 1)";
            string sqlGetID = "SELECT @@identity AS NewID FROM document";
            SqlConnection sqlConnection = new SqlConnection(Properties.Settings.Default.testlinqConnectionString);
            try
            {
                sqlConnection.Open();
                SqlCommand cmd = new SqlCommand(sqlDeletePerson_Documents, sqlConnection);
                cmd.CommandType = CommandType.Text;
                cmd.ExecuteNonQuery();
                cmd = new SqlCommand(sqlDeleteDocument, sqlConnection);
                cmd.ExecuteNonQuery();
                sqlConnection.Close();
            }
            catch (Exception)
            {
                MessageBox.Show("Ошибка инициализации БД SQL");
                return;
            }
            OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);
            try
            {
                sqlConnection.Open();
                excelConnection.Open();
                SqlCommand cmdInsertDocument = new SqlCommand(sqlInsertDocument, sqlConnection);
                cmdInsertDocument.CommandType = CommandType.Text;
                SqlCommand cmdInsertPerson = new SqlCommand(sqlInsertPerson, sqlConnection);
                cmdInsertPerson.CommandType = CommandType.Text;
                SqlCommand cmdGetId = new SqlCommand(sqlGetID, sqlConnection);
                cmdGetId.CommandType = CommandType.Text;
                if (excelConnection.State == ConnectionState.Open)
                {
                    object onumber, odate_registry, onum_registry, oenterprise, odescription;
                    string number;
                    string date_registry;
                    DateTime ddate_registry;
                    string num_registry;
                    string enterprise;
                    string description;

                    string excelQuery = "SELECT  [Номер], [дата поступления],[Вх# №],[Наименование организации],[Краткая характеристика] from [Лист1$]";
                    OleDbCommand cmd = new OleDbCommand(excelQuery,excelConnection);
                    OleDbDataReader reader = cmd.ExecuteReader();
                    while (reader.Read())
                    {
                        onumber = reader.GetValue(0);
                        number = (DBNull.Value == onumber) ? "" : onumber.ToString();
                        if ("" == number)
                            continue;
                        odate_registry = reader.GetValue(1);
                        date_registry = odate_registry.ToString().Replace(',', '.');
                        try
                        {
                            ddate_registry = Convert.ToDateTime(date_registry);
                        }
                        catch (Exception)
                        {
                            ddate_registry = new DateTime(1900, 1, 1);
                        }
                        onum_registry = reader.GetValue(2);
                        oenterprise = reader.GetValue(3);
                        odescription = reader.GetValue(4);

                        num_registry = (onum_registry == DBNull.Value) ? "" : onum_registry.ToString();
                        enterprise = (oenterprise == DBNull.Value) ? "" : oenterprise.ToString();
                        description = (odescription == DBNull.Value) ? "" : odescription.ToString();

                        cmdInsertDocument.Parameters.Clear();
                        cmdInsertDocument.Parameters.Add("@number", SqlDbType.VarChar).Value = number;
                        cmdInsertDocument.Parameters.Add("@regdate1", SqlDbType.DateTime).Value = ddate_registry;
                        cmdInsertDocument.Parameters.Add("@regnum1", SqlDbType.VarChar).Value = num_registry;
                        cmdInsertDocument.Parameters.Add("@description",SqlDbType.VarChar).Value = description;
                        cmdInsertDocument.Parameters.Add("@enterprise", SqlDbType.VarChar).Value = enterprise;
                        cmdInsertDocument.Parameters.Add("@kindDocument", SqlDbType.Int).Value = 2;
                        cmdInsertDocument.ExecuteNonQuery();
                        object id = cmdGetId.ExecuteScalar();

                        cmdInsertPerson.Parameters.Clear();
                        cmdInsertPerson.Parameters.Add("@id_person", SqlDbType.Int).Value = PersonComboBox.SelectedValue;
                        cmdInsertPerson.Parameters.Add("@id_document",SqlDbType.Int).Value = Convert.ToInt32(id);
                        cmdInsertPerson.Parameters.Add("@datetime",SqlDbType.DateTime).Value = dateTimePicker1.Value; 
                        cmdInsertPerson.Parameters.Add("@description", SqlDbType.VarChar).Value = "Первоначальная загрузка";
                        cmdInsertPerson.ExecuteNonQuery();
                    }

                    excelConnection.Close();
                    sqlConnection.Close();
                }
                else
                {
                    MessageBox.Show("Ошибка открытия excel файла");
                }
            }
            catch (Exception)
            {
                MessageBox.Show("Ошибка импорта из excel таблицы");
            }
            MessageBox.Show("Импорт данных из Excel таблицы завершен");
            Close();

        }
    }
}
